Q1.  What is  Database ?

⇒  A database is a computerized record-keeping system.

⇒  It is an organized collection of data, that allows people to quickly search and extract information in a way that meets their need; edit and delete records using computer software.

⇒  The data can include text, numbers, pictures or anything that can be stored in a computer.

Q2.  Describe the following three types of database.

1)  Single-table database (or) Flat database.

⇒  A Single-table database is a simple database that stores all data in a single table in rows and columns, also called as Flat database.

⇒  It contains unrelated tables like spreadsheet. It can be converted into text file separating each field using delimiters like comma, hyphen etc.

2)  Relational database.

⇒  A computerized record-keeping system that contains two or more tables of data, interrelated through key fields called relationships.

⇒  Relational database are very flexible for accessing, managing, and updating data.

3)  Distributed database.

⇒  Distributed database is relational database distributed over a computer network allowing multiple users to access data simultaneously.

Q3.  State the advantages and disadvantages of using database.

✬   Advantages 

⇒  Avoid data redundancy (duplication of data).

⇒  Increased data consistency (or correctness), allows only valid data defined by some rules.

⇒  Improve data access to users through use of host and query languages.

⇒  Improved data security to access the data through user credentials.

⇒  A database can be shared with number of users over the network.

✬   Disdvantages 

⇒  Database systems are complex, difficult, and time-consuming to design.

⇒  Extensive conversion costs in moving from a file-based system to a database system.

⇒  Initial training required for all programmers and users.

Q4.  Give three uses of database.

1. To store information about people, like pupils at school, patients in a hospital, etc.
2. To store information about things, like cars to be sold, books in a library, etc.
3. To store information about events, like hotel bookings, result of races, etc.

Q5. a)  What is (DBMS) Database Management System.

⇒  A database management system is a set of programs that allows the creation of data files called tables and permits the ways to process, alter and extract records.

It provides the following features :

• Allows the data to be stored by creating different tables which could be linked together.
• Allows to search records which meet certain criteria (conditions) using Query.
• Allows editing and deletion of records.
• Allows the alteration of structure of the table.
• Allows import and export of data.
• Allows to set security to access the data.

b)  Describe the following four main components of database management system.

✬    Table  :  A Table is a data structure, a basic unit of storing data in a database. It stores related data in rows called records with different piece of information in columns called fields of a record.

✬    Form  :  A Form is a database object, created for the user to enter, edit or display data from the data source table. It provides a user friendly interface for a database application.

✬    Query  :  A Query is a request used to extract data from a database according to the user’s request. It uses structured query language (SQL) that can be written or designed through query design grids.

✬    Report  :  A Report is an database object used to display and print formatted data in an organized manner form the database table.

Q6. a)  What is meant by  Record  and  Field  ?

⇒  Records is a basic data structure composed of different fields in a rows. Each record is a single row in the table that contains different information of an item. A set of records makes a database table.

⇒  Field refers to the columns, or data categories of same data type used by each record. It represents an attribute (a characteristic or quality) of some entity (like object, person, place, or event).

>

b)  Describe  Primary Key-field .

⇒  A primary key is a field in a table which uniquely identifies each record in a database table.

⇒  Primary keys must contain unique values.

⇒  A primary key column cannot have NULL values or left empty.

c)  Describe two purpose of primary key-field.

• It serves as a unique identifier for a row of data in a database table.
• It stops the possibility of duplicate data.
• It helps to speeds up queries, searches and sort requests.
• It is used to establish relationship with other tables by linking the fields.

Q7.  Describe how to create or design a database Table.

1. Organize data of each individual or thing in rows, with each column containing data of same data type.
2. In table design view, name each field with its appropriate data type.
3. Add validation check by specifying proper attributes like size (length of field), format, validation rule etc.
4. Identify the field which uniquely identifies each record and specify it as primary key field.
5. Save the table structure to store data later using datasheet view or form.

Q8.  Describe the following  Fields attributes (or properties)  that need to be decided before setting up the database to control how they receive data.

Q9. a)  Describe different  data types  used in designing a table.

b)  Give two reasons why choosing the correct data-type is important.

1. Do validation check over data while entering to prevent invalid characters being stored.
2. To check the correct format of data, like date and time to be stored in uniform way.

c)  Give two reasons why setting the field-size or maximum length of a field is important.

1. File size can be kept as small as possible.
2. Time taken to process data can be kept minimum.

Q10.  Codes are used to represent information and are useful, provided we know how to interpret them.

a)  Why Coding data is useful?

⇒  Codes are quicker to type in.

⇒  Using codes reduces the size of the files.

⇒  Increases the speed of search and process performed on the data.

⇒  Codes are often unique and are easy to search.

b)  Give three important factors that should be taken into account when designing codes.

1. Code should always be of same length.
2. Codes should contain enough details and should not be too short.
3. Codes should be easy to use.

Q11. a)  Describe what is meant by  Data Validation .

⇒  Validation is an automatic computer check to ensure that the data entered is sensible, reasonable and consistent. Data is accepted only if it falls withing the set rules. It does not check the accuracy of data.

b)  Describe how the following Validation-check works with example of its usage.

Q12. a)  What is meant by  SQL  query in database ?

⇒  SQL stands for Structured Query Language. It is a standard query language for writing scripts, to make request to the database for the records which meets the criteria we specify.

⇒  An SQL script is a list of SQL commands that performs a given task, often stored in a file so the script can be reused.

b)  State the benefits of using a query.

1. View data only from the fields you are interested in viewing.
2. Combine data from several data sources.
3. Use expressions as fields.
4. View records that meet criteria that you specify.

c)  Explain the function of each of the following SQL statements.

Q13.  Describe how to create SQL query to retrieve data from database table.

1. SQL query always begins with SELECT followed by FROM commands, which are mandatory in an SQL script.
2. All other commands are optional.
3. Start SQL script with SELECT command line, identify and write the name of fields to display separated by Comma (,).
4. Next specify the table-name in FROM command line.
5. Write the criteria or condition in WHERE command line.
6. Specify the name of the fields to sort in ORDER BY command line.
7. Put Semicolon ( ; ), to mark the end the SQL statement.

Examples of criteria or condition to specify in WHERE command line :

►   Exam-style Questions

Q14.  A database table, MARKS, is created with fields StdID, StdName, Sex, DOB, Grade, Eng, Dhi and Math to store student's details.

a)  (i)  State how many records and fields are there in the above table.

Records : 10         Fields : 8

(ii)  State which field you would choose for the primary key :  StdID

Give reason of your choice :  The field "StdID" contains unique data elements, which could be used to identify each record (all data elements are different).

b)  State what data type you would choose for the following fields.

c)  Write the SQL statement to display student's index, name and sex in ascending order of their name, grouped by sex listing male first female next, where marks scored in english is greater than 60.

SELECT  StdID,  StdName,  Sex

FROM  MARKS

WHERE  Eng > 60

ORDER BY  Sex DESC,  StdName ;

(Note : The field which has to be grouped should be sorted first.)

What would be output of the SQL query you have written above ?

d)  Write the SQL statement to display the name and marks scored in math subject of all girls of grade 10(A) in descending order of their marks in maths.

SELECT  StdName,  Math

FROM  MARKS

WHERE  Sex Like "F" AND  Grade Like "10(A)"

ORDER BY  Math DESC ;

e)  Write the SQL statement to display student's index, name and grade in descending order of their marks in dhivehi grouped by grade of all students, who has scored marks less than 45 and greater than or equal to 60 in dhivehi.

SELECT  StdID,  StdName,  Grade

FROM  MARKS

WHERE  Dhi < 45 OR Dhi >= 60

ORDER BY  Grade,  Dhi DESC ;

f)  Write the SQL statement to display student's index, name and sex in descending order of their marks in english, grouped by their gender female first, male next, who has scored marks between 60 and 75 (inclusive) in english.

SELECT  StdID,  StdName,  Sex

FROM  MARKS

WHERE  Eng >= 60 AND Eng <= 75

ORDER BY  Sex,  Eng DESC ;

(Note : Since criteria "WHERE Eng >= 60 AND Eng <= 75" includes the boundary values like 60, 61, 62,...75, you can also use the criteria, like WHERE Between 60 and 75 which will return the same records.)

g)  Write the SQL statement to display student's name only in descending order of marks in english, grouped by their grade, who has scored either marks greater than or equal to 50 in english or marks greater than 60 in dhivehi.

SELECT  StdName

FROM  MARKS

WHERE  Eng >= 50 OR Dhi > 60

ORDER BY  Grade,  Eng DESC ;

h)  Write the SQL statement to display student's name and grade, who has passed in all their three subjects with marks greater than or equal to 60.

SELECT  StdName

FROM  MARKS

WHERE  Eng >= 60 AND Dhi >= 60 AND Dhi >= 60 ;

i)  Write the SQL statement to display student's name and grade, who has scored marks greater than or equal to 85 in maths and marks greater than or equal to 60 in either english or dhivehi.

SELECT  StdName

FROM  MARKS

WHERE  Math >= 85 AND (Eng >= 60 OR Dhi >= 60) ;

j)  Write SQL statement to display student's index, name, grade, who belongs to the house other than Orange (like, Red, Green, Blue or Yellow).

SELECT  StdID,  StdName, Grade

FROM  MARKS

WHERE  House Not Like "Orange" ;

k)  Write the SQL statement to display student's name and grade, who belongs to either Red or Blue house only.

SELECT  StdID,  StdName,  Grade

FROM  MARKS

WHERE  House Like "Red" OR House Like "Blue" ;

l)  Write the SQL statement to return the name, grade and data of birth (DOB) of students who were born between 1st June 2004 and 30th October 2004.

SELECT  StdName,  Grade,  DOB

FROM  MARKS

WHERE  DOB >=  #6/1/2004#  AND  DOB <=  #10/30/2004# ;

// OR //

WHERE  DOB Between  #6/1/2004#  AND  #10/30/2004# ;

m)  Write the SQL statement to return the name, sex and total mark of english, dhivehi and math subject scored by each student of grade 10(A), listed in highest to lowest order of their total marks.

SELECT  StdName,  Sex,  (Eng + Dhi + Math) AS TotalMark

FROM  MARKS

WHERE  Grade Like "10(A)"

ORDER BY  (Eng + Dhi + Math)  DESC ;

Q15.  A database table, SALES, is used to keep a record of items made and sold by a furniture maker.

a)  Explain why the field Item number could not be used as a primary key.

⇒  All the fields contains repeated data element, so no field can uniquely identify each record and cannot be set as primary key field.

b)  Write the SQL statement to calculate and output the amount of money collected from delivered items.

SELECT  SUM(Amount) AS TotalAmt

FROM  SALES

WHERE  Status Like "Delivered" ;

What would be the output by the above SQL query ?

TotalAmt
6900

c)  Write the SQL statement to count and output how many of the items are in progress.

SELECT  COUNT(Status) AS NoItems

FROM  SALES

WHERE  Status Like "In progress" ;

What would be the output by the above SQL query ?

NoItems
2

d)  Write the SQL statement to count and output the number of items has collected amount more than 3500.

SELECT  COUNT(Amount) AS NoItems

FROM  SALES

WHERE  Amount > 3500 ;

What would be the output by the above SQL query ?

NoItems
4

Q16.  SQL statement WHERE which specifies the condition often include values from fields, these values need to be stated in a form that matches the data type for the field.

Tick(✔) the appropriate column to show how to provide values in criteria for a field to filter out the records.

Note : Data type Date/Time should be enclosed in hash (#) not single or double quote.
Data type Boolean store data using binary digits 0 and 1, so it should not be enclosed in single or double quote.

---

* * * * * * * * *
* * * * * *
* * *
*